package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
public class Cms_help {
	//插入
	@Test
	public void insert() {
		Connection conn=null;
		//准备数据库用户名，密码，url,driver
		String username="root";//用户名
		String password="root";//密码
		String driver="com.mysql.jdbc.Driver";//driver
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";//url
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="insert into cms_help(id,category_id,icon,title,show_status,create_time,read_count,content)values(1,2,33,44,23,'2018-10-08',22,88)";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//修改
	@Test
	public void  update() {
		Connection conn=null;
		//准备数据库用户名，密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		//协议://地址：端口号/数据库名？连接属性设置
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//属于反射的内容
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String  sql="update cms_help set icon='yia' where title=44";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row =st.executeUpdate(sql);
			System.out.println(row >0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
			     try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn !=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		
	}
	@Test
	public void delete() {
		Connection conn=null;
		//准备数据库用户名。密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		//协议://地址：端口号/数据库名？连接属性设置
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中,这是属于反射的内容
			Class.forName(driver);
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="delete from cms_help where title=44";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0? "删除成功":"删除失败");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//查询查一条
	@Test
	public void selet() {
		Connection conn = null;
		//1、准备数据库用户名、密码、url、driver
		String username = "root";
		String password = "root";
		String driver = "com.mysql.jdbc.Driver";
		//协议://地址:端口号/数据库名?连接属性设置
		String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取数据库连接java.sql.Connection
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
				String sql = "select * from cms_help";
				Statement st = null;
				ResultSet rs = null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st = conn.createStatement();
					//执行sql命令
					rs = st.executeQuery(sql);
					//处理查询结果集
					while(rs.next()) {
						//rs.getInt(columnIndex)：根据索引获取列的值，强烈不推荐
						//获取id列的值   id,category_id,icon,title,show_status,create_time,read_count,content
						//System.out.println(rs.getInt(1));
						//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
						System.out.println(rs.getInt("id"));
						System.out.println(rs.getInt("category_id"));
						System.out.println(rs.getInt("icon"));
						System.out.println(rs.getInt("title"));
						System.out.println(rs.getInt("show_status"));
						System.out.println(rs.getInt("create_time"));
						System.out.println(rs.getInt("read_count"));
						System.out.println(rs.getInt("content"));
						//获取username列
						//System.out.println(rs.getString("username"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if(rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if(st != null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if(conn != null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
	}
	}
	}
